package apig.servlet;

import java.sql.*;
import java.util.*;

import com.google.gwt.user.server.rpc.*;

import apig.client.serviface.*;
import apig.core.*;
import apig.servlet.access.*;
import apig.servlet.access.policy.*;

public class ApigServiceImpl {

	public final HashMap<Group, List<Student>> getAllStuds(Connection c)
			throws SQLException {
		HashMap<Group, List<Student>> res = new HashMap<Group, List<Student>>();

		try (PreparedStatement groupStat = c
				.prepareStatement("select group_id, name from groups");
				PreparedStatement studsStat = c
						.prepareStatement("select user_id, name, surname, patronimic from students, users where "
								+ "students.student_id = users.user_id and "
								+ "students.group_id = ?");) {
			ResultSet groupRS = groupStat.executeQuery();

			while (groupRS.next()) {
				Group g = new Group();
				g.id = groupRS.getInt(1);
				g.name = groupRS.getString(2);
				ArrayList<Student> sList = new ArrayList<Student>();
				studsStat.setInt(1, g.id);
				ResultSet studRS = studsStat.executeQuery();
				while (studRS.next()) {
					Student s = new Student();
					s.id = studRS.getInt(1);
					s.name = studRS.getString(2);
					s.surname = studRS.getString(3);
					s.patr = studRS.getString(4);
					sList.add(s);
				}
				res.put(g, sList);
			}

		}

		return res;
	}

	public final List<Code> getCodes(Connection c, CodeQuery cq)
			throws SQLException {

		if (cq.ownerId == CodeQuery.NULL) {
			return new ArrayList<Code>();
		}

		ArrayList<Code> codes = new ArrayList<Code>();

		try (PreparedStatement codesStatement = c
				.prepareStatement("select codes.code_id, codes.lab_id, codes.user_id, "
						+ "codes.revision, codes.name, codes.code, codes.checked, "
						+ "codes.time from codes, "
						+ "(select max(revision) as smaxrev, name as sname from "
						+ "(select * from codes "
						+ "where lab_id = ? and user_id = ? "
						+ "and revision <= ?) "
						+ "group by name) "
						+ "where name = sname and revision = smaxrev and user_id = ?");
				PreparedStatement verStatement = c
						.prepareStatement("select max(veracity) from hypos where "
								+ "hypos.code1_id = ?");
				PreparedStatement countStatement = c
						.prepareStatement("select count(*) from hypos where "
								+ "hypos.code1_id = ?");) {
			codesStatement.setInt(1, cq.labId);
			codesStatement.setInt(2, cq.ownerId);
			codesStatement.setInt(3, cq.revision);

			// workaround here because h2 seems to bug
			codesStatement.setInt(4, cq.ownerId);
			ResultSet codesRS = codesStatement.executeQuery();

			while (codesRS.next()) {
				Code code = new Code();
				code.codeId = codesRS.getInt(1);
				code.labId = codesRS.getInt(2);
				code.ownerId = codesRS.getInt(3);
				code.revision = codesRS.getInt(4);
				code.name = codesRS.getString(5);
				code.text = codesRS.getString(6);
				code.checked = codesRS.getBoolean(7);
				codes.add(code);
				verStatement.setInt(1, code.codeId);
				countStatement.setInt(1, code.codeId);
				ResultSet verRS = verStatement.executeQuery();
				verRS.next();
				code.veracity = verRS.getInt(1);
				ResultSet countRS = countStatement.executeQuery();
				countRS.next();
				code.hypoCount = countRS.getInt(1);
			}
		}

		return codes;
	}

	public final HashMap<Integer, List<Hypo>> getHypos(Connection c, int codeId)
			throws SQLException {
		HashMap<Integer, List<Hypo>> result = new HashMap<Integer, List<Hypo>>();
		try (PreparedStatement plagicodeStat = c
				.prepareStatement("select distinct code2_id from hypos where "
						+ "code1_id = ?");
				PreparedStatement listStat = c
						.prepareStatement("select veracity, code1_begin, code1_end, code2_begin, code2_end "
								+ "from hypos where "
								+ "code1_id = ? and code2_id = ?");) {
			plagicodeStat.setInt(1, codeId);
			listStat.setInt(1, codeId);
			ResultSet plagicodeRS = plagicodeStat.executeQuery();
			while (plagicodeRS.next()) {
				int code2Id = plagicodeRS.getInt(1);
				listStat.setInt(2, code2Id);
				ResultSet listRS = listStat.executeQuery();
				ArrayList<Hypo> l = new ArrayList<Hypo>();
				while (listRS.next()) {
					Hypo h = new Hypo();
					h.code1 = codeId;
					h.code2 = code2Id;
					h.v = listRS.getDouble(1);
					h.code1start = listRS.getObject(2) == null ? Hypo.UNKNOWN
							: listRS.getInt(2);
					h.code1end = listRS.getObject(3) == null ? Hypo.UNKNOWN
							: listRS.getInt(3);
					h.code2start = listRS.getObject(4) == null ? Hypo.UNKNOWN
							: listRS.getInt(4);
					h.code2end = listRS.getObject(5) == null ? Hypo.UNKNOWN
							: listRS.getInt(5);
					l.add(h);
				}
				result.put(code2Id, l);
			}
		}
		return result;
	}

	public final String getCodeOwner(Connection c, int codeId)
			throws SQLException {
		try (PreparedStatement s = c
				.prepareStatement("select users.name, surname, patronimic from users, codes where "
						+ "users.user_id = codes.user_id and "
						+ "codes.code_id = ?");) {
			s.setInt(1, codeId);
			ResultSet rs = s.executeQuery();
			if (rs.next()) {
				return rs.getString(3) + " " + rs.getString(1) + " "
						+ rs.getString(2);
			} else {
				return "[no user]";
			}
		}
	}

	public final Code getCode(Connection c, int codeId) throws SQLException {
		try (PreparedStatement cs = c
				.prepareStatement("select user_id, checked, name, code, revision from codes where codes.code_id = ?");) {
			cs.setInt(1, codeId);
			ResultSet crs = cs.executeQuery();
			if (crs.next()) {
				Code code = new Code();
				code.codeId = codeId;
				code.ownerId = crs.getInt(1);
				code.checked = crs.getBoolean(2);
				code.name = crs.getString(3);
				code.text = crs.getString(4);
				code.revision = crs.getInt(5);
				return code;
			} else {
				return null;
			}
		}
	}

	public final List<Discipline> getStudDisciplines(Connection c, int studId)
			throws SQLException {
		ArrayList<Discipline> ids = null;
		try (PreparedStatement getDisciplinesStatement = c
				.prepareStatement("select disciplines.discipline_id, "
						+ "disciplines.owner_id, disciplines.name "
						+ "from disciplines, discipline_orders, students, groups "
						+ "where disciplines.discipline_id = discipline_orders.discipline_id "
						+ "and discipline_orders.group_id = students.group_id "
						+ "and students.student_id = ?");) {
			// now get the list of ids for stud disciplines
			getDisciplinesStatement.setInt(1, studId);
			ResultSet discRS = getDisciplinesStatement.executeQuery();
			ids = new ArrayList<Discipline>();
			while (discRS.next()) {
				ids.add(new Discipline(discRS.getInt(1), discRS.getInt(2),
						discRS.getString(3)));
			}
		}
		return ids;
	}

	public final List<Lab> getDisciplineLabs(Connection c, int discId)
			throws SQLException {
		ArrayList<Lab> ids = null;

		try (PreparedStatement getLabsStatement = c
				.prepareStatement("select labs.lab_id, labs.name from labs "
						+ "where labs.discipline_id = ?")) {
			getLabsStatement.setInt(1, discId);
			ResultSet labRS = getLabsStatement.executeQuery();
			ids = new ArrayList<Lab>();
			while (labRS.next()) {
				ids.add(new Lab(labRS.getInt(1), discId, labRS.getString(2)));
			}
		}
		return ids;
	}

	public final void assignDiscipline(Connection c, int discId, int groupId,
			boolean assign) throws SQLException {
		try (

		PreparedStatement insertStatement = c
				.prepareStatement("insert into discipline_orders values (?,?)");) {
			insertStatement.setInt(1, discId);
			insertStatement.setInt(2, groupId);
			insertStatement.executeUpdate();
			// sum.notifyGroupSyllabusChange(groupId);
		}
	}

	public final void createDiscipline(Connection c, Discipline d, int ownerId)
			throws SQLException {
		try (PreparedStatement insertStatement = c
				.prepareStatement("insert into disciplines values (nextval('disciplines'),?,?)");) {
			insertStatement.setString(1, d.name);
			insertStatement.setInt(2, ownerId);
			insertStatement.executeUpdate();
		}
	}

	public final void removeDiscipline(Connection c, int discId)
			throws SQLException {
		try (PreparedStatement deleteStatement = c
				.prepareStatement("delete from disciplines where discipline_id = ?");
				PreparedStatement checkStatement = c
						.prepareStatement("select count(*) from discipline_orders where "
								+ "discipline_id = ?");) {
			checkStatement.setInt(1, discId);
			ResultSet checkRS = checkStatement.executeQuery();
			int count = checkRS.getInt(1);
			if (count > 0) {
				// do not the remove
				throw new IllegalArgumentException(
						"Cannot delete the ordered discipline");
			}
			deleteStatement.setInt(1, discId);
			deleteStatement.executeQuery();
		}
	}

	public final void createLab(Connection c, Lab l) throws SQLException {
		try (PreparedStatement groupsStatement = c.prepareStatement(""
				+ "select group_id " + "from disciplines "
				+ "where discipline_id = ?");
				PreparedStatement insertStatement = c
						.prepareStatement("insert into labs values (nextval('labs'), ?, ?)");) {
			insertStatement.setString(1, l.labName);
			insertStatement.setInt(2, l.discId);
			insertStatement.executeUpdate();
			groupsStatement.setInt(1, l.discId);
			ResultSet groupsRS = groupsStatement.executeQuery();
			// while (groupsRS.next()) {
			// sum.notifyGroupSyllabusChange(groupsRS.getInt(1));
			// }
		}
	}

	public final void removeLab(Connection c, int labId) throws SQLException {
		try (PreparedStatement groupsStatement = c
				.prepareStatement("select group_id from discipline_orders, labs "
						+ "where discipline_orders.discipline_id = labs.discipline_id "
						+ "and lab_id = ?");
				PreparedStatement deleteStatement = c
						.prepareStatement("select from labs where lab_id = ?");) {
			deleteStatement.setInt(1, labId);
			deleteStatement.executeUpdate();
			groupsStatement.setInt(1, labId);
			ResultSet groupsRS = groupsStatement.executeQuery();
			// while (groupsRS.next()) {
			// sum.notifyGroupSyllabusChange(groupsRS.getInt(0));
			// }
		}
	}

	public final HashMap<Discipline, List<Lab>> getStudLabs(Connection c,
			int studId) throws SQLException {

		HashMap<Discipline, List<Lab>> result = new HashMap<Discipline, List<Lab>>();

		try (PreparedStatement discStat = c
				.prepareStatement("select disciplines.discipline_id, disciplines.name "
						+ "from students, discipline_orders, disciplines "
						+ "where students.group_id = discipline_orders.group_id "
						+ "and disciplines.discipline_id = discipline_orders.discipline_id "
						+ "and students.student_id = ?");
				PreparedStatement labStat = c
						.prepareStatement("select lab_id, name from labs where "
								+ "discipline_id = ?");) {
			discStat.setInt(1, studId);
			ResultSet discRS = discStat.executeQuery();

			while (discRS.next()) {
				Discipline d = new Discipline();
				d.discId = discRS.getInt(1);
				d.name = discRS.getString(2);
				labStat.setInt(1, d.discId);
				ResultSet labRS = labStat.executeQuery();
				ArrayList<Lab> list = new ArrayList<Lab>();
				result.put(d, list);
				while (labRS.next()) {
					Lab l = new Lab();
					l.labId = labRS.getInt(1);
					l.labName = labRS.getString(2);
					l.discId = d.discId;
					list.add(l);
				}
			}

		}

		return result;
	}

	/**
	 * if user is already logged in and sends a request to log in again with
	 * some account (no matter the other or the current), he is automatically
	 * logged out and the logged in
	 * 
	 * Always returns true
	 */
	public final Boolean doLogin(Connection c, String login, String pass,
			String sessionId) throws SQLException {
		try (PreparedStatement logouterStatement = c
				.prepareStatement("delete from user_session_data "
						+ "where user_session_data.session_id = ?");
				PreparedStatement loginCheckerStatement = c
						.prepareStatement("select count(*) from users where user_id = ?");
				PreparedStatement loginerStatement = c
						.prepareStatement("insert into user_session_data "
								+ "values (?, ?, true, CURRENT_TIMESTAMP)");) {
			loginCheckerStatement.setInt(1, Integer.parseInt(login));
			ResultSet countRS = loginCheckerStatement.executeQuery();
			countRS.next();
			int count = countRS.getInt(1);
			if (count < 1) {
				throw new RuntimeException("Illegal login.");
			}
			logouterStatement.setString(1, sessionId);
			logouterStatement.executeUpdate();
			loginerStatement.setString(1, sessionId);
			loginerStatement.setInt(2, Integer.parseInt(login));
			loginerStatement.executeUpdate();
		}
		return true;
	}

	public final Boolean doLogout(Connection c, String sessionId)
			throws SQLException {

		try (PreparedStatement logouterStatement = c
				.prepareStatement("delete from user_session_data "
						+ "where user_session_data.session_id = ?");) {
			logouterStatement.setString(1, sessionId);
			logouterStatement.executeUpdate();
		}

		return true;
	}

	public static final UserData getUserData(Connection c, int userId)
			throws SQLException {
		UserData ud;

		PreparedStatement userStatement = null;
		PreparedStatement groupStatement = null;
		try {
			userStatement = c
					.prepareStatement("select user_id, name, surname, patronimic, role, password "
							+ "from users where user_id = ?");
			groupStatement = c.prepareStatement("select group_id from ");
			userStatement.setInt(1, userId);
			ResultSet userRS = userStatement.executeQuery();
			if (userRS.next()) {
				ud = new UserData();
				ud.userId = userRS.getInt(1);
				ud.name = userRS.getString(2);
				ud.surname = userRS.getString(3);
				ud.patronimic = userRS.getString(4);
				ud.role = userRS.getInt(5);
				ud.password = userRS.getString(6);

				if (ud.role == UserData.ROLE_STUD) {

				}
			} else {
				ud = null;
			}
		} finally {
			try {
				if (userStatement != null) {
					userStatement.close();
				}
			} finally {
				if (groupStatement != null)
					groupStatement.close();
			}
		}
		return ud;
	}

	public static final UserData getUserData(String sessionId) throws SQLException {
		try (Connection c = ServerThings.getThings().getConnection();) {
			return getUserData(c, sessionId);
		}
	}

	public static final UserData getUserData(Connection c, String sessionId)
			throws SQLException {
		UserData ud = new UserData();

		PreparedStatement sessionStatement = null;
		PreparedStatement userStatement = null;
		PreparedStatement groupStatement = null;

		try {
			sessionStatement = c
					.prepareStatement("select user_session_data.user_id, user_session_data.logged_in "
							+ "from user_session_data "
							+ "where user_session_data.session_id = ?");
			userStatement = c
					.prepareStatement("select users.name, users.surname, users.patronimic, "
							+ "users.role, users.password "
							+ "from users "
							+ "where users.user_id = ?");
			groupStatement = c
					.prepareStatement("select groups.name from groups, students "
							+ "where groups.group_id = students.group_id "
							+ "and students.student_id = ?");
			sessionStatement.setString(1, sessionId);
			ResultSet sessionRS = sessionStatement.executeQuery();
			if (sessionRS.next()) {
				int userId = sessionRS.getInt(1);
				ud.userId = userId;
				ud.logged = sessionRS.getBoolean(2);
				userStatement.setInt(1, userId);
				ResultSet userRS = userStatement.executeQuery();
				if (!userRS.next()) {
					//
				}
				ud.name = userRS.getString(1);
				ud.surname = userRS.getString(2);
				ud.patronimic = userRS.getString(3);
				ud.role = userRS.getInt(4);
				ud.password = userRS.getString(5);
				if (ud.role == UserData.ROLE_STUD) {
					groupStatement.setInt(1, userId);
					ResultSet groupRS = groupStatement.executeQuery();
					if (!groupRS.next()) {
						//
					}
					ud.groupName = groupRS.getString(1);
				}
			}
			ud.sessionId = sessionId;

		} finally {

			try {
				if (sessionStatement != null)
					sessionStatement.close();
			} finally {
				try {
					if (userStatement != null)
						userStatement.close();
				} finally {
					if (groupStatement != null)
						groupStatement.close();
				}
			}
		}

		return ud;
	}
}
